package com.itmg.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.springframework.orm.ibatis.support.SqlMapClientDaoSupport;

import com.itmg.dao.CacheDAO;
import com.itmg.dao.bean.ConditionBean;
import com.itmg.dao.model.CacheModel;
import com.itmg.dao.model.DataModel;
import com.itmg.util.Constants;
import com.itmg.vo.SearchResultItemVO;
public class CacheDAOImpl extends SqlMapClientDaoSupport implements CacheDAO {
	
	//插入到数据库中
	public void saveData(ConditionBean conditionBean) throws Exception{
		 Connection con = null;
		 String sql ="insert into shop_cache (" +
			 		"productMd5," +
			 		"product_name," +
			 		"product_id," +
			 		"comment," +
			 		"product_url," +
			 		"maker_name," +
			 		"small_image_url," +
			 		"big_image_url," +
			 		"lowest_price," +
			 		"max_price," +
			 		"my_price," +
			 		"shop_name," +
			 		"shop_url," +
			 		"source_site_name," +
			 		"count," +
			 		"now_price," +
			 		"page_total," +
			 		"total," +
			 		"evaluation," +
			 		"create_time) values (" +
			 		"?," +
			 		"?," +
			 		"?," +
			 		"?," +
			 		"?," +
			 		"?," +
			 		"?," +
			 		"?," +
			 		"?," +
			 		"?," +
			 		"?," +
			 		"?," +
			 		"?," +
			 		"?," +
			 		"?," +
			 		"?," +
			 		"?," +
			 		"?," +
			 		"?," +
			 		"?)";
		 try{
			 con = getSqlMapClientTemplate().getDataSource().getConnection();
			 PreparedStatement ps = con.prepareStatement(sql);
			 for (SearchResultItemVO view : conditionBean.getSearchResultItemList()) {
				ps.setString(1, conditionBean.getProductMd5());
				ps.setString(2, view.getProductName());
				ps.setString(3, view.getProductID());
				ps.setString(4, view.getComment());
				ps.setString(5, view.getProductUrl());
				ps.setString(6, view.getMakerName());
				ps.setString(7, view.getSmallImageUrl());
				ps.setString(8, view.getBigImageUrl());
				ps.setInt(9, view.getLowestPrice());
				ps.setInt(10, view.getMaxPrice());
				ps.setDouble(11, view.getMyPrice());
				ps.setString(12, view.getShopName());
				ps.setString(13, view.getShopUrl());
				ps.setString(14, view.getSourceSiteName());
				ps.setInt(15, view.getCount());
				ps.setInt(16, view.getNowPrice());
				ps.setInt(17,conditionBean.getSearchResultItemList().size());
				ps.setInt(18,conditionBean.getTotal());
				if(view.getEvaluation() != null)
					ps.setDouble(19,view.getEvaluation());
				else
					ps.setDouble(19,0.0);
				ps.setTimestamp(20, conditionBean.getCreate_time());
				ps.addBatch();
			}
			 ps.executeBatch();
		 }catch(Exception e){
			 e.printStackTrace();
		 }finally{
			 try {
				con.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		 }
	}

	//根据md5找到对应的所有数据
	public List<SearchResultItemVO> getCacheModelByProductMd5(ConditionBean conditionBean) throws Exception{
		List<SearchResultItemVO> list = new ArrayList<SearchResultItemVO>();
		String sql = "";
		if(conditionBean.getMinPrice() != null || conditionBean.getMaxPrice() != null){
			if(conditionBean.getMaxPrice().intValue() == 0){
				if(Constants.SORT_PRICE_UP_TO_DOWN.equals(conditionBean.getSort()))
					sql = "select * from shop_cache where productMd5 = ? " +
							"and lowest_price>? order by lowest_price desc limit ?,? ";
				else if(Constants.SORT_PRICE_DOWN_TO_UP.equals(conditionBean.getSort()))
					sql = "select * from shop_cache where productMd5 = ? " +
							"and lowest_price>? order by lowest_price limit ?,? ";
				else if(Constants.SORT_KEYWORDS_HEAT.equals(conditionBean.getSort()))
					sql = "select * from shop_cache where productMd5 = ? " +
					      "and lowest_price>? order by evaluation desc limit ?,? ";
				else
					sql = "select * from shop_cache where productMd5 = ? " +
							"and lowest_price>? limit ?,? ";
			}else{
				if(Constants.SORT_PRICE_UP_TO_DOWN.equals(conditionBean.getSort()))
					sql = "select * from shop_cache where productMd5 = ? " +
							"and lowest_price BETWEEN ? and ? order by lowest_price desc limit ?,? ";
				else if(Constants.SORT_PRICE_DOWN_TO_UP.equals(conditionBean.getSort()))
					sql = "select * from shop_cache where productMd5 = ? " +
							"and lowest_price BETWEEN ? and ? order by lowest_price limit ?,? ";
				else if(Constants.SORT_KEYWORDS_HEAT.equals(conditionBean.getSort()))
					sql = "select * from shop_cache where productMd5 = ? " +
					      "and lowest_price BETWEEN ? and ? order by evaluation desc limit ?,? ";
				else
					sql = "select * from shop_cache where productMd5 = ? " +
							"and lowest_price BETWEEN ? and ? limit ?,? ";
			}
			
		}else{
			if(Constants.SORT_PRICE_UP_TO_DOWN.equals(conditionBean.getSort()))
				sql = "select * from shop_cache where productMd5 = ? order by lowest_price desc limit ?,? ";
			else if(Constants.SORT_PRICE_DOWN_TO_UP.equals(conditionBean.getSort()))
				sql = "select * from shop_cache where productMd5 = ? order by lowest_price limit ?,? ";
			else 
				sql = "select * from shop_cache where productMd5 = ? limit ?,? ";
		}
		Connection con = null;
		try{
			con = getSqlMapClientTemplate().getDataSource().getConnection();
			PreparedStatement ps = con.prepareStatement(sql);
			ps.setString(1, conditionBean.getProductMd5());
			if(conditionBean.getMinPrice() != null || conditionBean.getMaxPrice() != null){
				if(conditionBean.getMaxPrice().intValue()==0){
					ps.setInt(2, conditionBean.getMinPrice());
					ps.setInt(3, conditionBean.getStart());
					ps.setInt(4,conditionBean.getEnd());
				}else{
					ps.setInt(2, conditionBean.getMinPrice());
					ps.setInt(3,conditionBean.getMaxPrice());
					ps.setInt(4, conditionBean.getStart());
					ps.setInt(5,conditionBean.getEnd());
				}
			}else{
				ps.setInt(2, conditionBean.getStart());
				ps.setInt(3,conditionBean.getEnd());
			}
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				SearchResultItemVO model = new SearchResultItemVO();
				model.setProductName(rs.getString("product_name"));
				model.setProductID(rs.getString("product_id"));
				model.setComment(rs.getString("comment"));
				model.setProductUrl(rs.getString("product_url"));
				model.setMakerName(rs.getString("maker_name"));
				model.setSmallImageUrl(rs.getString("small_image_url"));
				model.setBigImageUrl(rs.getString("big_image_url"));
				model.setLowestPrice(rs.getInt("lowest_price"));
				model.setMaxPrice(rs.getInt("max_price"));
				model.setMyPrice(rs.getDouble("my_price"));
				model.setShopName(rs.getString("shop_name"));
				model.setShopUrl(rs.getString("shop_url"));
				model.setSourceSiteName(rs.getString("source_site_name"));
				model.setCount(rs.getInt("count"));
				model.setNowPrice(rs.getInt("now_price"));
				model.setEvaluation(rs.getDouble("evaluation"));
				list.add(model);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try {
				con.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return list;
	}
	
	//得到所有的数据
	public List<DataModel> getCacheModelAll() throws Exception{
		List<DataModel> list = new ArrayList<DataModel>();
		String sql = "select * from shop_cache";
		Connection con = null;
		try{
			con = getSqlMapClientTemplate().getDataSource().getConnection();
			Statement ps = con.createStatement();
			ResultSet rs = ps.executeQuery(sql);
			while(rs.next()){
				DataModel model = new DataModel();
				model.setProduct_name(rs.getString("product_name"));
				model.setProduct_id(rs.getString("product_id"));
				model.setComment(rs.getString("comment"));
				model.setProduct_url(rs.getString("product_url"));
				model.setMaker_name(rs.getString("maker_name"));
				model.setSmall_image_url(rs.getString("small_image_url"));
				model.setBig_image_url(rs.getString("big_image_url"));
				model.setLowest_price(rs.getInt("lowest_price"));
				model.setMax_price(rs.getInt("max_price"));
				model.setMy_price(rs.getDouble("my_price"));
				model.setShop_name(rs.getString("shop_name"));
				model.setShop_url(rs.getString("shop_url"));
				model.setSource_site_name(rs.getString("source_site_name"));
				model.setCount(rs.getInt("count"));
				model.setNow_price(rs.getInt("now_price"));
				model.setPage_total(rs.getInt("page_total"));
				model.setTotal(rs.getInt("total"));
				model.setEvaluation(rs.getDouble("evaluation"));
				model.setCreate_time(rs.getTimestamp("create_time"));
				list.add(model);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try {
				con.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return list;
	}
	
	//统计价格范围的数据数量
	public Integer getCountByPrice(ConditionBean conditionBean) throws Exception{
		Connection con = null;
		String sql = "";
		if(conditionBean.getMaxPrice().intValue() != 0)
			sql = "select count(*) from shop_cache where productMd5 = ? and lowest_price BETWEEN ? and ?";
		else
			sql = "select count(*) from shop_cache where productMd5 = ? and lowest_price>?";
		Integer count = 0;
		try {
			con = getSqlMapClientTemplate().getDataSource().getConnection();
			PreparedStatement ps = con.prepareStatement(sql);
			ps.setString(1, conditionBean.getProductMd5());
			if(conditionBean.getMaxPrice().intValue() != 0){
				ps.setInt(2, conditionBean.getMinPrice());
				ps.setInt(3, conditionBean.getMaxPrice());
			}else
				ps.setInt(2, conditionBean.getMinPrice());
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				count = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				con.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return count;
		
	}
	
	//删除所有数据
	public void deleteAll() throws Exception{
		Connection con = null;
		String sql = "delete from shop_cache";
		try {
			con = getSqlMapClientTemplate().getDataSource().getConnection();
			Statement ps = con.createStatement();
			ps.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				con.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
	
	//加载所有的MD5信息
	public List<CacheModel> getProductMd5All() throws Exception{
		List<CacheModel> list = new ArrayList<CacheModel>();
		Connection con = null;
		String sql = "select productMd5,page_total,total from shop_cache group by productMd5,page_total";
		try {
			con = getSqlMapClientTemplate().getDataSource().getConnection();
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(sql);
			while(rs.next()){
				CacheModel model= new CacheModel();
				model.setProductMd5(rs.getString("productMd5"));
				model.setPage_total(rs.getInt("page_total"));
				model.setTotal(rs.getInt("total"));
				list.add(model);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				con.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return list;
	}
	
}
